﻿using System.Collections.Generic;
using Microsoft.AspNetCore.Mvc;
using Bll;
using Common;
using Microsoft.AspNetCore.Authorization;
using Web.Controllers;
using Web.Filter;
using Model;
using Web.Extension;
using System;
using Web.Util;
using System.Data;
using System.Linq;

namespace Web.Teach.Controllers
{
    [Route("api/teach/[controller]/[action]")]
    [ApiController]
    [Authorize("teacher")]
    [QueryFilter]
    public class UsualScoreController : MyBaseController<UsualScore>
    {
        IUsualScoreBll bll;
        public IUsualScoreLogBll usualScoreLogBll { get; set; }
        public IUsualScoreItemBll usualScoreItemBll { get; set; }
        public UsualScoreController(IUsualScoreBll bll)
        {
            this.bll = bll;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="where"></param>
        /// <param name="CourseId">必填</param>
        /// <param name="ClasssId">必填</param>
        /// <returns></returns>
        [HttpGet]
        public Result List([FromQuery] Dictionary<string, string> where, int CourseId, int ClasssId)
        {
            return Result.Success("succeed").SetData(bll.SelectAllOrInit(CourseId, ClasssId, MyUser.SchoolId.GetValueOrDefault(), MyUser.Id));
        }
        /// <summary>
        /// 修改平时成绩，增加平时成绩项
        /// </summary>
        /// <param name="usualScoreId"></param>
        /// <param name="usualScoreItemId"></param>
        /// <returns></returns>
        [HttpPost]
        public Result Update([FromQuery]int usualScoreId, [FromQuery] int usualScoreItemId)
        {
            UsualScore usualScore = bll.SelectOne(usualScoreId);
            UsualScoreItem usualScoreItem = usualScoreItemBll.SelectOne(usualScoreItemId);
            usualScore.Score += usualScoreItem.Value;
            //开启事务
            bll.DbContext().Database.BeginTransaction();
            bool ret = bll.Update(usualScore);
            UsualScoreLog log = new UsualScoreLog() { SchoolId = MyUser.SchoolId == 0 ? null : MyUser.SchoolId, ScoreId = usualScore.Id, Name = usualScoreItem.Name, Value = usualScoreItem.Value, Sys = false };
            usualScoreLogBll.Add(log);
            bll.DbContext().Database.CommitTransaction();//结束事务
            return ModelState.IsValid ? (ret ? Result.Success("修改成功").SetData(null) : Result.Error("修改失败")) : Result.Error("修改失败!" + ModelState.GetAllErrMsgStr(";")); ;
        }
        [HttpGet]
        public ActionResult ExportToExcel(int CourseId, int ClasssId)
        {
            var st = OfficeHelper.DataTableToExcel(CreateTable(CourseId,  ClasssId));
            //string filePath = DataTableToExcel(CreateTable());
            // FileStream fs = new FileStream(filePath, FileMode.Open);
            //return File(fs, "application/vnd.ms-excel", "异地派遣面试记录表.xls");
            //var provider = new FileExtensionContentTypeProvider();
            //var memi = provider.Mappings[fileExt];
            //FileStreamResult fileStreamResult = new FileStreamResult(stream, memi) { FileDownloadName = fileName };

            return File(st, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "平时成绩表.xlsx");
        }
        //创建DataTable 方法 
        private DataTable CreateTable(int CourseId, int ClasssId)
        {

            string[] Headers = { "ID", "学号", "姓名", "成绩", "课程" };
            DataTable dataTable = new DataTable("ExportData");//*新建一张表
            foreach (string TemStr in Headers)
            {
                DataColumn strNameColumn = new DataColumn();
                strNameColumn.DataType = typeof(String);
                strNameColumn.ColumnName = TemStr;
                dataTable.Columns.Add(strNameColumn);       //*建立五列数据
            }
            var db = bll.DbContext();
            var query = from u in db.UsualScores
                        join s in db.Students on u.StudentId equals s.Id into students
                        from student in students.DefaultIfEmpty()
                        join c in db.Courses on u.CourseId equals c.Id into courses
                        from course in courses.DefaultIfEmpty()
                        where u.CourseId== CourseId && student.ClasssId== ClasssId
                        orderby student.Id ascending
                        select new { id = student.Id, sn = student.Sn, name = student.Realname, score = u.Score, course = course.Name };
            var objs = query.ToList();
            for (int i = 0, len = objs.Count; i < len; i++)
            {
                var o = objs[i];
                DataRow rowData = dataTable.NewRow();   //*建立行数据
                rowData["ID"] = (i + 1).ToString();
                rowData["学号"] = o.GetType().GetProperty("sn")?.GetValue(o)?.ToString();
                rowData["姓名"] = o.GetType().GetProperty("name")?.GetValue(o)?.ToString();
                rowData["成绩"] = o.GetType().GetProperty("score")?.GetValue(o)?.ToString();
                rowData["课程"] = o.GetType().GetProperty("course")?.GetValue(o)?.ToString();

                dataTable.Rows.Add(rowData);
            }
            return dataTable;
        }
    }
}
